import pymysql
from datetime import datetime
from typing import Dict, Any, Optional

class DBHelper:
    """
    数据库操作助手类（MySQL版本）
    """
    
    def __init__(self, host: str = '47.104.247.126', port: int = 3306, user: str = 'SMS', password: str = 'lp123456aA', database: str = 'SMS'):
        """
        初始化数据库连接参数
        
        Args:
            host: 数据库主机地址
            port: 数据库端口
            user: 数据库用户名
            password: 数据库密码
            database: 数据库名称
        """
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
    
    def get_connection(self):
        """
        获取数据库连接
        
        Returns:
            pymysql.Connection对象
        """
        return pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            database=self.database,
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
    
    def get_latest_send_record_by_phone(self, phone: str) -> Optional[Dict[str, Any]]:
        """
        根据手机号获取最新的发送记录（用于验证码验证）
        
        Args:
            phone: 电话号码
            
        Returns:
            最新的发送记录字典，如果没有找到则返回None
        """
        print(f"[DEBUG] 开始执行get_latest_send_record_by_phone，手机号: {phone}")
        
        # 检查参数
        if not phone:
            print(f"[DEBUG] 手机号为空")
            return None
            
        conn = None
        cursor = None
        
        try:
            # 打印数据库连接信息（不包含密码）
            print(f"[DEBUG] 准备连接数据库 - host: {self.host}, port: {self.port}, user: {self.user}, db: {self.database}")
            
            # 获取连接
            conn = self.get_connection()
            print(f"[DEBUG] 数据库连接成功")
            
            # 验证连接状态
            if not conn or not conn.open:
                print(f"[DEBUG] 数据库连接无效或已关闭")
                return None
                
            # 简单的查询语句
            query = "SELECT ID, PHONE, IP, DDATE, ccode FROM send WHERE PHONE = %s ORDER BY DDATE DESC LIMIT 1"
            print(f"[DEBUG] 准备执行查询: {query}")
            print(f"[DEBUG] 查询参数: {phone}")
            
            # 创建游标并执行查询
            cursor = conn.cursor()
            print(f"[DEBUG] 游标创建成功")
            
            # 执行查询
            affected_rows = cursor.execute(query, (phone,))
            print(f"[DEBUG] 查询执行成功，影响行数: {affected_rows}")
            
            # 获取结果
            result = cursor.fetchone()
            print(f"[DEBUG] 查询结果类型: {type(result)}, 内容: {result}")
            
            # 处理结果
            if result:
                print(f"[DEBUG] 找到记录，准备处理DDATE字段")
                
                # 检查并转换DDATE字段
                if 'DDATE' in result:
                    print(f"[DEBUG] DDATE字段存在，值: {result['DDATE']}, 类型: {type(result['DDATE'])}")
                    
                    # 只有当DDATE不是datetime对象且不为空时才转换
                    if result['DDATE'] and not isinstance(result['DDATE'], datetime):
                        try:
                            # 尝试不同的日期格式
                            date_str = str(result['DDATE'])
                            print(f"[DEBUG] 尝试转换DDATE字符串: {date_str}")
                            # 尝试多种可能的日期格式
                            try:
                                result['DDATE'] = datetime.strptime(date_str, '%Y-%m-%d %H:%M:%S')
                                print(f"[DEBUG] DDATE转换成功 (格式1)")
                            except ValueError:
                                try:
                                    result['DDATE'] = datetime.strptime(date_str, '%Y-%m-%d %H:%M:%S.%f')
                                    print(f"[DEBUG] DDATE转换成功 (格式2)")
                                except ValueError:
                                    # 如果无法转换，保持原始值但记录警告
                                    print(f"[DEBUG] 警告: 无法转换DDATE格式")
                        except Exception as date_e:
                            print(f"[DEBUG] DDATE转换异常: {str(date_e)}")
                else:
                    print(f"[DEBUG] 警告: 结果中没有DDATE字段")
            else:
                print(f"[DEBUG] 未找到匹配的记录")
            
            return result
            
        except pymysql.MySQLError as mysql_err:
            # 捕获MySQL特定错误
            print(f"[DEBUG] MySQL错误: {mysql_err}")
            print(f"[DEBUG] MySQL错误编号: {mysql_err.args[0]}")
            print(f"[DEBUG] MySQL错误信息: {mysql_err.args[1]}")
            # 重新抛出异常
            raise
            
        except Exception as e:
            # 捕获其他所有异常
            print(f"[DEBUG] 异常类型: {type(e).__name__}")
            print(f"[DEBUG] 异常消息: {str(e)}")
            import traceback
            print(f"[DEBUG] 异常堆栈: {traceback.format_exc()}")
            # 重新抛出异常
            raise
            
        finally:
            # 确保游标关闭
            if cursor:
                try:
                    cursor.close()
                    print(f"[DEBUG] 游标已关闭")
                except Exception as close_err:
                    print(f"[DEBUG] 关闭游标时出错: {str(close_err)}")
            
            # 确保连接关闭
            if conn:
                try:
                    conn.close()
                    print(f"[DEBUG] 数据库连接已关闭")
                except Exception as close_err:
                    print(f"[DEBUG] 关闭连接时出错: {str(close_err)}")
    
    def get_latest_send_record(self, ip: str, phone: str) -> Optional[Dict[str, Any]]:
        """
        获取最新的发送记录（用于发送短信）
        
        Args:
            ip: IP地址
            phone: 电话号码
            
        Returns:
            记录字典，如果没有记录则返回None
        """
        query = """
        SELECT * FROM send 
        WHERE ID = (SELECT MAX(ID) FROM send WHERE IP = %s OR PHONE = %s)
        """
        
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, (ip, phone))
                return cursor.fetchone()
    
    def get_monthly_count(self, ip: str, phone: str) -> int:
        """
        获取当月发送次数
        
        Args:
            ip: IP地址
            phone: 电话号码
            
        Returns:
            发送次数
        """
        query = """
        SELECT COUNT(1) as count FROM send 
        WHERE (IP = %s OR PHONE = %s) 
        AND YEAR(DDATE) = YEAR(NOW()) 
        AND MONTH(DDATE) = MONTH(NOW())
        """
        
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, (ip, phone))
                result = cursor.fetchone()
                return result['count'] if result else 0
                
    def get_daily_count(self, ip: str, phone: str) -> int:
        """
        获取当日发送次数
        
        Args:
            ip: IP地址
            phone: 电话号码
            
        Returns:
            发送次数
        """
        query = """
        SELECT COUNT(1) as count FROM send 
        WHERE (IP = %s OR PHONE = %s) 
        AND DATE(DDATE) = CURDATE()
        """
        
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, (ip, phone))
                result = cursor.fetchone()
                return result['count'] if result else 0
    
    def insert_send_record(self, ip: str, phone: str, code: str, task_id: str = None) -> None:
        """
        插入发送记录
        
        Args:
            ip: IP地址
            phone: 电话号码
            code: 验证码
            task_id: 任务ID
        """
        query = """
        INSERT INTO send (PHONE, IP, DDATE, ccode, taskId) 
        VALUES (%s, %s, NOW(), %s, %s)
        """
        
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, (phone, ip, code, task_id))
                conn.commit()
    
    # 地区相关方法
    def insert_or_update_region(self, data: dict) -> dict:
        """
        插入或更新地区信息 - 按照用户要求修改
        如果id为0或空，则新增地区；否则修改现有地区
        
        Args:
            data: 包含地区信息的字典，格式: {"id": 0, "nation": "国家", "number": "10"}
            
        Returns:
            更新后的地区信息
        """
        # 获取id值
        region_id = data.get('id')
        nation = data.get('nation')
        number = data.get('number')
        
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                # 检查id是否为0或空
                if region_id and region_id > 0:
                    # 先检查地区是否存在
                    existing_region = self.get_region_by_id(region_id)
                    if not existing_region:
                        return {'error': '地区不存在'}
                    
                    # 更新操作
                    query = """
                    UPDATE region SET nation = %s, number = %s 
                    WHERE id = %s
                    """
                    cursor.execute(query, (nation, number, region_id))
                    conn.commit()
                    
                    # 无论是否有实际更新，都返回最新的地区信息
                    # cursor.rowcount == 0 可能是因为数据没有变化，而不是地区不存在
                    result = self.get_region_by_id(region_id)
                    # 如果调用方需要sort字段，添加一个默认值
                    if result and 'sort' not in result:
                        result['sort'] = 0
                    return result
                else:
                    # 新增操作
                    query = """
                    INSERT INTO region (nation, number) 
                    VALUES (%s, %s)
                    """
                    cursor.execute(query, (nation, number))
                    conn.commit()
                    result = {'id': cursor.lastrowid, 'nation': nation, 'number': number}
                    # 如果调用方需要sort字段，添加一个默认值
                    result['sort'] = 0
                    return result
    
    def get_region_by_id(self, region_id: int) -> dict:
        """
        根据ID获取地区信息
        
        Args:
            region_id: 地区ID
            
        Returns:
            地区信息字典
        """
        query = "SELECT * FROM region WHERE id = %s"
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, (region_id,))
                return cursor.fetchone()
    
    def get_region_by_nation(self, nation: str) -> dict:
        """
        根据国家名称获取地区信息
        
        Args:
            nation: 国家名称
            
        Returns:
            地区信息字典
        """
        query = "SELECT * FROM region WHERE nation = %s"
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, (nation,))
                return cursor.fetchone()
    
    def get_all_regions(self, page: int = 1, page_size: int = 10, sort_field: str = None, sort_order: str = None) -> list:
        """
        获取所有地区信息（支持分页和排序）
        
        Args:
            page: 页码，从1开始
            page_size: 每页数量
            sort_field: 排序字段（可选）
            sort_order: 排序方向（可选，默认为asc）
            
        Returns:
            地区信息列表
        """
        # 计算偏移量
        offset = (page - 1) * page_size
        
        # 构建查询语句，避免使用不存在的sort字段
        base_query = "SELECT * FROM region"
        
        # 添加排序条件
        order_by = "ORDER BY sort ASC"
        if sort_field:
            # 验证排序字段的合法性（只允许安全的字段名）
            valid_fields = ['id', 'nation', 'number', 'sort', 'created_at', 'updated_at']
            if sort_field in valid_fields:
                direction = 'ASC' if sort_order and sort_order.lower() == 'asc' else 'DESC'
                order_by = f"ORDER BY {sort_field} {direction}"
        
        # 添加分页
        query = f"{base_query} {order_by} LIMIT %s OFFSET %s"
        
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, (page_size, offset))
                return cursor.fetchall()
    
    # 经销商相关方法
    def insert_or_update_distributor(self, data: dict) -> dict:
        """
        插入或更新经销商信息
        
        Args:
            data: 包含经销商信息的字典
            
        Returns:
            更新后的经销商信息，lat和lng字段确保为数字类型
        """
        distributor_id = data.get('id')
        
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                if distributor_id and distributor_id > 0:
                    # 更新操作 - 只更新提供的字段
                    update_fields = []
                    params = []
                    
                    # 定义可更新的字段
                    updatable_fields = ['sale', 'nation', 'distributorName', 'lat', 'lng',
                                       'email', 'phone', 'whatsApp', 'url', 'address',
                                       'ico', 'distance', 'banner', 'state']
                    
                    # 遍历字段，只添加非None的值
                    for field in updatable_fields:
                        if field in data:
                            update_fields.append(f"{field} = %s")
                            params.append(data[field] if field != 'state' else data.get('state', 100))
                    
                    # 如果没有提供要更新的字段，直接返回当前记录
                    if not update_fields:
                        return self.get_distributor_by_id(distributor_id)
                    
                    # 添加WHERE条件
                    params.append(distributor_id)
                    
                    # 构建查询
                    query = f"""
                    UPDATE distributor SET 
                        {', '.join(update_fields)}
                    WHERE id = %s
                    """
                    
                    cursor.execute(query, params)
                    conn.commit()
                    return self.get_distributor_by_id(distributor_id)
                else:
                    # 插入操作
                    query = """
                    INSERT INTO distributor (
                        sale, nation, distributorName, lat, lng, email,
                        phone, whatsApp, url, address, ico, distance,
                        banner, state
                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    """
                    params = (
                        data.get('sale'), data.get('nation'), data.get('distributorName'),
                        data.get('lat'), data.get('lng'), data.get('email'),
                        data.get('phone'), data.get('whatsApp'), data.get('url'),
                        data.get('address'), data.get('ico'), data.get('distance'),
                        data.get('banner'), data.get('state', 100)
                    )
                    cursor.execute(query, params)
                    conn.commit()
                    return self.get_distributor_by_id(cursor.lastrowid)
    
    def get_distributor_by_id(self, distributor_id: int) -> dict:
        """
        根据ID获取经销商信息
        
        Args:
            distributor_id: 经销商ID
            
        Returns:
            经销商信息字典，lat和lng字段确保为数字类型
        """
        query = "SELECT * FROM distributor WHERE id = %s"
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, (distributor_id,))
                distributor = cursor.fetchone()
                # 确保lat和lng字段为数字类型
                if distributor:
                    distributor = self._convert_coordinates_to_number(distributor)
                return distributor
    
    def delete_distributor(self, distributor_id: int) -> bool:
        """
        删除经销商信息
        
        Args:
            distributor_id: 经销商ID
            
        Returns:
            是否删除成功
        """
        query = "DELETE FROM distributor WHERE id = %s"
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                affected_rows = cursor.execute(query, (distributor_id,))
                conn.commit()
                return affected_rows > 0
    
    def _convert_coordinates_to_number(self, distributor: dict) -> dict:
        """
        将经销商信息中的经纬度字段转换为数字类型
        
        Args:
            distributor: 经销商信息字典
            
        Returns:
            转换后的经销商信息字典
        """
        # 转换lat字段
        if 'lat' in distributor and distributor['lat'] is not None:
            try:
                distributor['lat'] = float(distributor['lat'])
            except (ValueError, TypeError):
                # 如果转换失败，保持原值
                pass
        
        # 转换lng字段
        if 'lng' in distributor and distributor['lng'] is not None:
            try:
                distributor['lng'] = float(distributor['lng'])
            except (ValueError, TypeError):
                # 如果转换失败，保持原值
                pass
        
        return distributor
    
    def search_distributors(self, nation: str = None, distributor_name: str = None, address: str = None, page: int = 1, page_size: int = 10) -> list:
        """
        搜索经销商信息（支持分页）
        
        Args:
            nation: 国家名称
            distributor_name: 经销商名称
            address: 地址
            page: 页码，从1开始
            page_size: 每页数量
            
        Returns:
            经销商信息列表，lat和lng字段确保为数字类型
        """
        conditions = []
        params = []
        
        if nation:
            conditions.append("nation LIKE %s")
            params.append(f"%{nation}%")
        if distributor_name:
            conditions.append("distributorName LIKE %s")
            params.append(f"%{distributor_name}%")
        if address:
            conditions.append("address LIKE %s")
            params.append(f"%{address}%")
        
        # 计算偏移量
        offset = (page - 1) * page_size
        params.extend([page_size, offset])
        
        query = "SELECT * FROM distributor"
        if conditions:
            query += " WHERE " + " AND ".join(conditions)
        query += " ORDER BY id LIMIT %s OFFSET %s"
        
        with self.get_connection() as conn:
            with conn.cursor(pymysql.cursors.DictCursor) as cursor:
                cursor.execute(query, params)
                distributors = cursor.fetchall()
                # 确保每个经销商的lat和lng字段为数字类型
                for i in range(len(distributors)):
                    distributors[i] = self._convert_coordinates_to_number(distributors[i])
                return distributors
    
    def get_distributors_by_nation(self, nation: str, page: int = 1, page_size: int = 10) -> list:
        """
        根据国家获取经销商信息（支持分页）
        
        Args:
            nation: 国家名称
            page: 页码，从1开始
            page_size: 每页数量
            
        Returns:
            经销商信息列表，lat和lng字段确保为数字类型
        """
        # 计算偏移量
        offset = (page - 1) * page_size
        
        query = "SELECT * FROM distributor WHERE nation = %s ORDER BY id LIMIT %s OFFSET %s"
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, (nation, page_size, offset))
                distributors = cursor.fetchall()
                # 确保每个经销商的lat和lng字段为数字类型
                for i in range(len(distributors)):
                    distributors[i] = self._convert_coordinates_to_number(distributors[i])
                return distributors
                
    def search_distributors_simple(self, nation: str = None, distributor_name: str = None, page: int = 1, page_size: int = 10) -> list:
        """
        搜索经销商信息，只返回特定字段（distributorName、lat、lng、id、ico、nation）
        
        Args:
            nation: 国家名称（可选）
            distributor_name: 经销商名称（可选）
            page: 页码，从1开始
            page_size: 每页数量
            
        Returns:
            经销商信息列表，只包含指定字段，lat和lng字段确保为数字类型
        """
        # 计算偏移量
        offset = (page - 1) * page_size
        
        # 构建查询语句和参数
        query = "SELECT distributorName, lat, lng, id, ico, nation, sort FROM distributor"
        conditions = []
        params = []
        
        # 添加筛选条件
        if nation:
            conditions.append("nation = %s")
            params.append(nation)
        
        if distributor_name:
            conditions.append("distributorName LIKE %s")
            params.append(f"%{distributor_name}%")
        
        # 添加WHERE子句
        if conditions:
            query += " WHERE " + " AND ".join(conditions)
        
        # 添加排序和分页
        query += " ORDER BY sort, id LIMIT %s OFFSET %s"
        params.extend([page_size, offset])
        
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, params)
                distributors = cursor.fetchall()
                # 确保每个经销商的lat和lng字段为数字类型
                for i in range(len(distributors)):
                    distributors[i] = self._convert_coordinates_to_number(distributors[i])
                return distributors
                
    def get_distributors_simple_total_count(self, nation: str = None, distributor_name: str = None) -> int:
        """
        获取简化版经销商总数，支持按国家和名称筛选
        
        Args:
            nation: 国家名称（可选）
            distributor_name: 经销商名称（可选）
            
        Returns:
            经销商总数
        """
        query = "SELECT COUNT(*) as count FROM distributor"
        conditions = []
        params = []
        
        # 添加筛选条件
        if nation:
            conditions.append("nation = %s")
            params.append(nation)
        
        if distributor_name:
            conditions.append("distributorName LIKE %s")
            params.append(f"%{distributor_name}%")
        
        # 添加WHERE子句
        if conditions:
            query += " WHERE " + " AND ".join(conditions)
        
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, params)
                result = cursor.fetchone()
                return result.get('count', 0) if result else 0